#!/usr/bin/env python
# -*- coding: utf-8 -*-

import _mysql, _mysql_exceptions
from datetime import date, timedelta


def connect_to_dianpu(db='dbwww58com_shopemc'):
    host = '192.168.10.82'
    port = 58885
    user = 'shopuser_fenxi'
    passwd = 'mYmq6U.^~V'

    dbh = _mysql.connect(host, user, passwd, db, port)
    return dbh


def connect_to_shangpin(db='dbwww58com_emcproduct_0'):
    host = '192.168.10.82'
    port = 58885
    user = 'emcproduct_hprro'
    passwd = 'OqX1jJ3Qjy'

    # dbh = _mysql.connect(host, user, passwd, db, port, init_command='set names utf8')
    dbh = _mysql.connect(host, user, passwd, db, port)
    return dbh


def connect_to_dingdan(db='dbwww58com_emcorder'):
    host = '192.168.10.82'
    port = 58885
    user = 'emcorder_hprro'
    passwd = 'Yb%LEfmLO5'

    dbh = _mysql.connect(host, user, passwd, db, port)
    return dbh


def connect_to_order_stream(db='groupbuy', init_command=None):
    host = '10.5.20.15'
    port = 5029
    user = 'groupbuy_stats'
    passwd = 'L#c8UM5RGh5C'

    if init_command is not None:
        dbh = _mysql.connect(host, user, passwd, db, port, init_command=init_command)
    else:
        dbh = _mysql.connect(host, user, passwd, db, port)

    return dbh


def connect_to_shop_stats(db='dbwww58com_shop_stats'):
    host = '10.5.20.16'
    port = 58885
    user = 'ec_stat'
    passwd = 'L#c8UM5RGh5C'

    dbh = _mysql.connect(host, user, passwd, db, port)
    return dbh


def connect_to_groupbuy():
    return connect_to_shop_stats('dbwww58com_groupbuy_stats')


def connect_to_58_stats():
    return connect_to_shop_stats('dbwww58com_58_stats')


def connect_to_base_dicts():
    return connect_to_shop_stats('dbwww58com_base_dicts')


def query(dbh, q):
    try:
        dbh.query(q)
    except _mysql.MySQLError, e:
        print e
        print q
        return None

    result = dbh.store_result()
    if result is None:  # the sql no response
        return True
    rows = result.fetch_row(0)
    return rows


def droptable(dbh, table):
    q = 'drop table %s' % (table)
    query(dbh, q)


def get_a_list_of_string(dbh, q):
    result = query(dbh, q)
    if(result is None):
        return []
    return [a[0] for a in result]


def get_a_dict(dbh, q):
    info = {}

    try:
        r = query(dbh, q)
    except:
        return {}

    for one in r:
        info[one[0]] = one[1] if len(one) == 2 else list(one[1:])

    return info


def get_a_string(dbh, q):
    try:
        r = query(dbh, q)[0][0]
    except IndexError, e:
        # print 'get_a_string Error: ' + str(e)
        # print q
        return None
    return r


def get_a_number(dbh, q):
    r = get_a_string(dbh, q)
    if r is None:
        return 0

    try:
        n = int(r)
    except:
        n = float(r)

    return n


def get_a_value(dbh, q):
    r = query(dbh, q)[0][0]
    try:
        n = int(r)
    except:
        try:
            n = float(r)
        except:
            n = r

    return n


def insertinto(dbh, tablename, field, value, dryrun=False):
    if len(field) != len(value):
        print 'the len of field is not equal len of value:'
        print '*' * 60
        print field
        print value
        print '*' * 60
        return False

    q = "INSERT INTO %s (%s) VALUES (%s);" % (tablename, ','.join(field), ','.join(map(lambda x: "'" + str(x) + "'", value)))
    if dryrun:
        print q
        return True

    try:
        dbh.query(q)
    except _mysql_exceptions.IntegrityError, e:
        print e
        return False

    return True


def update(dbh, tablename, field, value, restriction, dryrun=False):
    # sometimes the value is just a int or float
    if isinstance(value, int) or isinstance(value, float):
        pass
    elif len(field) != len(value):
        print 'the len of field is not equal len of value:'
        print '*' * 60
        print field
        print value
        print '*' * 60
        return False

    if isinstance(value, int) or isinstance(value, float):
        updatefield = field + '=' + '"' + str(value) + '"'
    else:
        updatefield = ','.join([f + '=' + '"' + str(v) + '"' for f, v in zip(field, value)])

    q = 'UPDATE %s SET %s WHERE %s;' % (tablename, updatefield, restriction)
    if dryrun:
        print q
        return True

    try:
        dbh.query(q)
    except _mysql_exceptions.IntegrityError, e:
        print e
        return False

    return True


def str_day_start_and_end(y, m, d, h=None):
    start = date(y, m, d)
    end = start + timedelta(1)
    start = start.isoformat()
    end = end.isoformat()

    if h is not None:
        end = start + " %02d:59:00" % (h)
        start += " %02d:00:00" % (h)

    return start, end


if __name__ == '__main__':
    print connect_to_dingdan()
    print connect_to_shangpin()
    print connect_to_dingdan()
    print connect_to_shop_stats()
    print connect_to_groupbuy()
    print connect_to_order_stream()
    print str_day_start_and_end(2010, 3, 13)
    print str_day_start_and_end(2010, 3, 13, h=2)
